package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.City;
import by.bsu.touragency.db.beans.Country;
import by.bsu.touragency.db.connection.DBConnector;

public class CityDAO extends AbstractDAO {
	public static final String SQL_CITY_CREATE = "INSERT INTO tourAgencyDB.city (cityName, countryId, about)" + "VALUES (?, ?, ?);";
	public static final String SQL_CITY_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.city " + " WHERE cityName = ? AND countryId = ?;";
	public static final String SQL_CITY_READ_BY_ID = "SELECT * FROM tourAgencyDB.city " + "WHERE (id= ?);";
	public static final String SQL_CITY_DELETE = "DELETE FROM tourAgencyDB.city WHERE id = ?";
	public static final String SQL_CITY_UPDATE = "UPDATE tourAgencyDB.city  SET cityName = ? WHERE (countryId = ?) AND (about = ?);";
	public static final String SQL_CITY_IS_EXIST_NAME = "SELECT COUNT(*) AS rowcount FROM tourAgencyDB.city " + "WHERE (cityName = ?);";

	private static Logger logger = Logger.getLogger("log");

	public boolean isExistCityName(City city) {
		logger.info("Is city with this name exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CITY_IS_EXIST_NAME);
			int n;
			try {// 3
				statement.setString(1, city.getCityName());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("This city already exist");
				return true;
			} else {
				logger.debug("It is new city");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	// C
	public City create(City city) {
		try {

			PreparedStatement statement;
			if (!isExistCityName(city)) {
				logger.info("Create city");
				connector = new DBConnector();
				statement = connector.getPreparedStatement(SQL_CITY_CREATE);
				statement.setString(1, city.getCityName());
				statement.setInt(2, city.getCountry().getId());
				statement.setString(3, city.getAbout());
				statement.executeUpdate();
			}
			statement = connector.getPreparedStatement(SQL_CITY_CREATE_GET_ID);
			statement.setString(1, city.getCityName());
			statement.setInt(2, city.getCountry().getId());
			resultSet = statement.executeQuery();

			if (resultSet.next()) {
				city.setId(resultSet.getInt("id"));
			}

		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return city;
	}

	public City readById(int id) {
		City city = new City();
		try {
			logger.info("Read city by id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CITY_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				city.setId(resultSet.getInt("id"));
				city.setCityName(resultSet.getString("cityName"));
				city.setAbout(resultSet.getString("about"));
				Country c = new Country();
				CountryDAO cd = new CountryDAO();
				c = cd.readById(resultSet.getInt("countryId"));
				city.setCountry(c);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}

		return city;
	}

	public void update(City city) {
		try {
			logger.info("Update city info");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CITY_UPDATE);
			statement.setString(1, city.getCityName());
			statement.setInt(2, city.getCountry().getId());
			statement.setString(3, city.getAbout());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void deleteById(int id) {
		try {
			logger.info("Delete city by id");
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_CITY_DELETE);
			statement.setInt(1, id);
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}
}
